package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oocl.dao.CustomerDao;
import com.oocl.dao.FavoriteDao;
import com.oocl.formV1.util.DBUtil;
import com.oocl.pojo.Customer;
import com.oocl.pojo.Favorite;

public class CustomerDaoImpl implements CustomerDao{

	@Override
	public int addCustomer(Customer customer) {
		String sql = "insert into customers(id, name, sal, birth, sex) values(cust_Seq.nextval, ?, ?, ?, ?)";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, customer.getName());
			pst.setDouble(2, customer.getSal());
			pst.setDate(3, new java.sql.Date(customer.getBirth().getTime()));
			pst.setBoolean(4, customer.isSex());;
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
		return m;
	}
	/**
	 * @param id
	 * @param name
	 * @param sal
	 * @param sex
	 * @param birth
	 * @param favorites
	 */
	
	
	@Override
	public List<Customer> findAllCustomers() {
		FavoriteDao favoriteDao = new FavoriteDaoImpl();
		
		List<Customer> list = new ArrayList<Customer>();
		String sql = "select DISTINCT c.id id, c.name name, c.sal sal, c.sex sex, c.birth birth from CUSTOMERS c "
				+ "left join CUST_FAVO cf on c.id = cf.cust_id "
				+ "left join FAVORITES f on cf.favo_id = f.id";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();
			while (rs.next()) {
				int custId = rs.getInt("id");
				System.out.println("custId-=--------"+custId);
				List<Favorite> favorites = favoriteDao.findByCustId(custId);
				Customer cust=new Customer(custId, rs.getString("name"), rs.getDouble("sal"), rs.getBoolean("sex"), new java.sql.Date(rs.getDate("birth").getTime()), favorites);
				list.add(cust);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return list;
	}
	
	
	@Override
	public int getLastId() {
		int lastId = 0;
		List<Customer> list = new ArrayList<Customer>();
		String sql = "select max(id) max_id from CUSTOMERS";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				lastId = rs.getInt("max_id");
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return lastId;
	}
	
	
	@Override
	public boolean delByCustId(int cust_id) {
		String sql = "delete from CUSTOMERS where id = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, cust_id);
			rs = pst.executeQuery();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return false;
	}

}
